package com.net.core.core.sql;


import com.net.core.core.YXException;
import com.net.core.utils.MyUtils;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class YxSqlPlus<T> extends YxSql {


    private ObjReflect reflect = null;
    Class<T> clazz;

    private boolean isAddFlag = false;
    private String orAnd = "and";
    String tableName;


    public Class<T> getReturnClass() {
        return clazz;
    }

    public YxSqlPlus(Class<T> clazz) {
        this.clazz = clazz;
        this.reflect = new ObjReflect(clazz);
        tableName = reflect.getTableName();
    }


    public Class<T> getClazz() {
        Type type = getClass().getGenericSuperclass();
        Type trueType = ((ParameterizedType) type).getActualTypeArguments()[0];
        return (Class<T>) trueType;
    }

    /**
     * sql.select().or(sql.eq(key,value).in(key,value1,value2));
     *
     * @param sql
     */
    public void or(YxSqlPlus sql) {
        orStart();
        sql.orEnd();
    }

    /**
     * @param column
     * @param date   "yyyy-MM-dd"
     * @return
     */
    public YxSqlPlus<T> eqDay(String column, String date) {
        addOrAnd().append("DateDiff(dd," + column + ",?)=0").addValue(date);
        return this;
    }

    public YxSqlPlus<T> eqDay(SFunction<T, ?> column, String date) {
        return eqDay(getColumn(column), date);
    }


    /**
     * @param column
     * @param year   "yyyy"
     * @return
     */
    public YxSqlPlus<T> eqYear(String column, String year) {
        addOrAnd().append("DateDiff(yy," + column + ",?)=0").addValue(year);
        return this;
    }

    public YxSqlPlus<T> eqYear(SFunction<T, ?> column, String year) {
        return eqYear(getColumn(column), year);
    }


    /**
     * @param column
     * @param month  "yyyy-MM"
     * @return
     */
    public YxSqlPlus<T> eqMonth(String column, String month) {
        if (month.length() == 7) month += "-01";
        addOrAnd().append("DateDiff(mm," + column + ",?)=0").addValue(month);
        return this;
    }

    public YxSqlPlus<T> eqMonth(SFunction<T, ?> column, String values) {
        return eqMonth(getColumn(column), values);
    }


    /**
     * in 获取listB中 对象的 key字段的值 来作为 column 的入参
     *
     * @param listB
     * @param <B>
     * @return
     */
    public <B> YxSqlPlus<T> in(SFunction<T, ?> keyA, List<B> listB, SFunction<B, ?> keyB) {
        String column1 = LambdaUtils.getColumn(keyA);
        in(column1, listB, keyB);
        return this;
    }

    public YxSqlPlus<T> in(String column, Object... value) {
        in(column, Arrays.asList(value));
        return this;
    }

    public YxSqlPlus<T> in(SFunction<T, ?> column, Object... value) {
        String column1 = LambdaUtils.getColumn(column);
        in(column1, Arrays.asList(value));
        return this;
    }


    /**
     * @param column
     * @param value
     * @param SFunction
     * @param <B>
     * @return
     */
    public <B> YxSqlPlus<T> in(String column, List<B> value, SFunction<B, ?> SFunction) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("in (");
        for (int i = 0; i < value.size(); i++) {
            stringBuffer.append("?");
            addValue(SFunction.apply(value.get(i)));
            if (i != value.size() - 1) {
                stringBuffer.append(",");
            }
        }
        stringBuffer.append(")");
        addOrAnd().append(column).append(stringBuffer.toString());
        return this;
    }

    public YxSqlPlus<T> in(SFunction<T, ?> column, List values) {
        return in(getColumn(column), values);
    }

    public YxSqlPlus<T> in(String column, List value) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("in (");
        for (int i = 0; i < value.size(); i++) {
            stringBuffer.append("?");
            addValue(value.get(i));
            if (i != value.size() - 1) {
                stringBuffer.append(",");
            }
        }
        stringBuffer.append(")");
        addOrAnd().append(column).append(stringBuffer.toString());
        return this;
    }

    public YxSqlPlus<T> orStart() {
        orAnd = "or";
        append("and (");
        isAddFlag = true;
        return this;
    }

    public YxSqlPlus<T> orEnd() {
        append(")");
        orAnd = "and";
        return this;
    }

    /**
     * 使用方式 ：
     * public SysUser getUserByUsername(String username){
     * YxSqlPlus<SysUser> sql = getSQL(SysUser.class);
     * sql.select().eq(SysUser::getUsername,username);
     * return select(sql,SysUser.class);
     * }
     *
     * @param column
     * @param value
     * @return
     */
    public YxSqlPlus<T> eq(SFunction<T, ?> column, Object value) {
        return eq(getColumn(column), value);
    }

    public YxSqlPlus<T> eq(String column, Object value) {
        if (value == null) {
            addOrAnd().append(column).append("is null");
        } else {
            addOrAnd().append(column).append("=?").addValue(value);
        }

        return this;
    }

    /**
     * 不等于
     *
     * @param column
     * @param value
     */
    public YxSqlPlus<T> notEq(String column, Object value) {
        if (value == null) {
            addOrAnd().append(column).append("is not null");
        } else {
            addOrAnd().append(column).append("!=?").addValue(value);
        }
        return this;
    }

    public YxSqlPlus<T> notEq(SFunction<T, ?> column, Object value) {
        return notEq(getColumn(column), value);
    }

    public void and(String s, Object value) {
        append("and").append(s).addValue(value);
    }

    public YxSqlPlus<T> addOrAnd() {
        if (sql.toString().trim().endsWith("where")) {
            return this;
        } else if (sql.toString().trim().endsWith("WHERE")) {
            return this;
        }
        if (isAddFlag) {//如果是括号开始，就不需要 and 和 or
            isAddFlag = false;
        } else {//如果是不，就添加 or 或者 and
            append(orAnd);
        }
        return this;
    }

    public YxSqlPlus<T> like(SFunction<T, ?> column, String value) {
        return like(getColumn(column), value);
    }

    public YxSqlPlus<T> like(String column, String name) {
        addOrAnd().append(column).append("like ?").addValue("%" + name.trim() + "%");
        return this;
    }

    //请用 updateWhere
    @Deprecated
    public YxSql update(Object obj) {
        append("update").append(getTableName()).append("set");
        Map<String, Field> getFields = reflect.getFields();
        try {
            for (Map.Entry<String, Field> entry : getFields.entrySet()) {
                entry.getValue().setAccessible(true);
                append(entry.getKey()).append("=?,").addValue(entry.getValue().get(obj));
            }
            delLast(",");
            append("where");
        } catch (Exception e) {
            throw new YXException(e);
        }
        return this;
    }

    public YxSqlPlus<T> updateWhere(Object obj) {
        append("update").append(getTableName()).append("set");
        Map<String, Field> getFields = reflect.getFields();
        try {
            for (Map.Entry<String, Field> entry : getFields.entrySet()) {
                Field value = entry.getValue();
                TableField annotation = value.getAnnotation(TableField.class);
                if (annotation != null && annotation.auto()) continue;
                entry.getValue().setAccessible(true);
                append(entry.getKey()).append("=?,").addValue(entry.getValue().get(obj));
            }
            delLast(",");
            append("where");
        } catch (Exception e) {
            throw new YXException(e);
        }
        return this;
    }


    public YxSqlPlus<T> updateById(T obj) {
        updateWhere(obj);
        try {
            Map<String, Field> ids = reflect.getTableIds();
            for (Map.Entry<String, Field> entry : ids.entrySet()) {
                entry.getValue().setAccessible(true);
                append(entry.getKey()).append("=? and").addValue(entry.getValue().get(obj));
            }
            delLast("and");
        } catch (Exception e) {
            throw new YXException(e);
        }
        return this;
    }

    public YxSqlPlus<T> updateById(List<T> obj) {
        updateWhere(obj);
        try {
            Map<String, Field> ids = reflect.getTableIds();
            for (Map.Entry<String, Field> entry : ids.entrySet()) {
                entry.getValue().setAccessible(true);
                append(entry.getKey()).append("=? and").addValue(entry.getValue().get(obj));
            }
            delLast("and");
        } catch (Exception e) {
            throw new YXException(e);
        }
        return this;
    }

    public YxSqlPlus<T> deleteById(T obj) {
        append("DELETE FROM").append(getTableName()).append("where");
        try {
            Map<String, Field> ids = reflect.getTableIds();
            for (Map.Entry<String, Field> entry : ids.entrySet()) {
                entry.getValue().setAccessible(true);
                append(entry.getKey()).append("=? and").addValue(entry.getValue().get(obj));
            }
            delLast("and");
        } catch (Exception e) {
            throw new YXException(e);
        }
        return this;
    }

    public YxSqlPlus<T> deleteById(String id) {
        append("DELETE FROM").append(getTableName()).append("where");
        try {
            Map<String, Field> ids = reflect.getTableIds();
            if (ids.size() > 1) throw new YXException("多个主键请选择 对象方式删除");
            for (Map.Entry<String, Field> entry : ids.entrySet()) {
                entry.getValue().setAccessible(true);
                append(entry.getKey()).append("=? and").addValue(id);
            }
            delLast("and");
        } catch (Exception e) {
            throw new YXException(e);
        }
        return this;
    }


    /**
     * @param columns 参数必须是 key value 形式("key","value","key","value")
     * @return
     */
    public YxSqlPlus<T> updateKeyValue(Object... columns) {
        return updateKey(columns);
    }


    public YxSqlPlus<T> updateKeyValue(SFunction<T, ?> fun, Object column) {
        String key = LambdaUtils.getColumn(fun);
        return updateKey(key, column);
    }


    /**
     * 参数必须是 key value 形式
     * 使用 updateKeyValue 代替
     *
     * @param value ("key","value","key","value")
     * @return
     */
    @Deprecated
    public YxSqlPlus<T> updateKey(Object... value) {
        append("update ");
        append(getTableName());
        append("set ");
        if (value.length % 2 != 0) throw new YXException("参数必须key，value成对出现");
        for (int i = 0; i < value.length; i++) {
            if (i % 2 == 0) {
                append((String) value[i]).append(" = ? , ");
            } else {
                addValue(value[i]);
            }
        }
        delLast(",");
        append("where");
        return this;
    }


    public YxSqlPlus<T> delete() {
        append("DELETE FROM").append(getTableName()).append("where ");
        return this;
    }


    protected YxSqlPlus<T> insert(Object object, Map<String, Field> fieldAll) {
        append("insert into ").append(getTableName());
        append("(");
        try {
            for (Map.Entry<String, Field> item : fieldAll.entrySet()) {
                Field value = item.getValue();
                TableId idAnno = value.getAnnotation(TableId.class);
                //如果是自增长 插入就不使用id
                if (idAnno != null && idAnno.autoincrement()) {
                    continue;
                }

                item.getValue().setAccessible(true);
                Object o = item.getValue().get(object);
                if (o == null) {
                    continue;
                }
                addValue(o);
                param(item.getKey()).append(",");
            }
        } catch (Exception e) {
            throw new YXException(e.getMessage(), e);
        }
        delLast(",");
        append(") values (");
        for (int i = 0; i < values.size(); i++) {
            append("?,");
        }
        delLast(",");
        append(")");
        return this;
    }


    /**
     * @param t
     * @return
     */
    public YxSqlPlus<T> insert(T t) {
        if (t instanceof List) {
            throw new YXException("该方法不支持批量插入，可以使用 batchInsert 插入");
        }
        Map<String, Field> fieldAll = reflect.getFieldAll();
        return insert(t, fieldAll);
    }

    public YxSqlPlus<T> insertAutoId(T t) {
        Map<String, Field> fieldAll = reflect.getFields();
        return insert(t, fieldAll);
    }

    /**
     * 插入，不包括id
     *
     * @param object
     * @return
     */
    public YxSqlPlus<T> insertWithoutId(Object object) {
        Map<String, Field> fields = reflect.getFields();
        return insert(object, fields);
    }

    public Map<String, Field> getTablePrimaryKey() {
        Map<String, Field> fields = reflect.getTableIds();
        return fields;
    }

    public YxSqlPlus<T> selectTop(int index) {
        append("select top " + index);
        Map<String, Field> fieldAll = reflect.getFieldAll();
        for (Map.Entry<String, Field> entry : fieldAll.entrySet()) {
            sql.append(entry.getKey()).append(",");
        }
        delLast(",");
        append("  FROM ").append(getTableName()).append(" WITH(NOLOCK) where 1=1");
        return this;
    }

    /**
     * @param param 字符串 "paramA,paramB,paramC"
     * @return
     */
    public YxSqlPlus<T> select(String param) {
        append("select ").append(param);
        append("  FROM ").append(getTableName()).append(" WITH(NOLOCK) where 1=1");
        return this;
    }


    public YxSqlPlus<T> select(SFunction<T, ?>... columns) {
        return select(getColumn(columns));
    }

    /**
     * @param columns 字符串组 "columnA","columnB"
     * @return
     */
    public YxSqlPlus<T> select(String... columns) {
        append("select ");
        for (int i = 0; i < columns.length; i++) {
            append(columns[i]).append(",");
        }
        delLast(",");
        append("  FROM ").append(getTableName()).append(" WITH(NOLOCK) where 1=1");
        return this;
    }

    public YxSqlPlus<T> select() {
        append("select");
        Map<String, Field> fieldAll = reflect.getFieldAll();
        for (Map.Entry<String, Field> entry : fieldAll.entrySet()) {
            sql.append(entry.getKey()).append(",");
        }
        delLast(",");
        append("  FROM ").append(getTableName()).append(" WITH(NOLOCK) where 1=1");
        return this;
    }

    public void orderBy(SFunction<T, ?>... keys) {
        orderBy(getColumn(keys));
    }

    public void orderBy(String... columns) {
        if (columns.length == 0) return;
        sql.append(" order by ");
        for (String column : columns) {
            sql.append(column).append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" ");
    }

    public void orderByDesc(SFunction<T, ?>... keys) {
        orderByDesc(getColumn(keys));
    }

    public void orderByDesc(String... columns) {
        if (columns.length == 0) return;
        sql.append(" order by ");
        for (String column : columns) {
            sql.append(column).append(" desc,");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" ");
    }

    protected YxSql getUpdateSql(Object obj, SFunction<T, ?>... keys) {
        return getUpdateSql(obj, getColumn(keys));
    }

    protected YxSql getUpdateSql(Object obj, String... keys) {
        YxSql yxSql = new YxSql();
        try {
            ObjReflect objRef = new ObjReflect(obj);
            Map<String, Field> fieldAll = objRef.getFieldAll();
            String tableName = objRef.getTableName();
            yxSql.append("update  ").append(getTableName()).append(" set ");
            for (String key : keys) {
                yxSql.append(key).append("= ? ,");
                fieldAll.get(key).setAccessible(true);
                yxSql.addValue(fieldAll.get(key).get(obj));
                fieldAll.remove(key);
            }
            yxSql.delLast(",");
            yxSql.append(" where  ");
            for (Map.Entry<String, Field> entry : fieldAll.entrySet()) {
                yxSql.append(entry.getKey()).append("= ? and");
                entry.getValue().setAccessible(true);
                yxSql.addValue(entry.getValue().get(obj));
            }
            yxSql.delLast("and");
        } catch (Exception e) {
            throw new YXException(e);
        }
        return yxSql;
    }


    /**
     * 将产生
     *
     * @param object
     * @throws IllegalAccessException
     */
    public void obtionData(Object object) {
        Map<String, Field> fieldAll = reflect.getFieldAll();
        for (Map.Entry<String, Field> item : fieldAll.entrySet()) {
            item.getValue().setAccessible(true);
            try {
                addValue(item.getValue().get(object));
            } catch (IllegalAccessException e) {
                throw new YXException(e.getMessage(), e);
            }
        }
    }

    public YxSqlPlus<T> count(String value) {
        append(" SELECT count(" + value + ") FROM").append(getTableName()).append("WITH(NOLOCK) where 1=1");
        return this;
    }

    public YxSqlPlus<T> count() {
        append(" SELECT count(*) FROM").append(getTableName()).append("WITH(NOLOCK) where 1=1");
        return this;
    }


    public YxSqlPlus<T> gt(String column, Object value) {
        addOrAnd().append(column).append(">?").addValue(value);
        return this;
    }

    public YxSqlPlus<T> gt(SFunction<T, ?> column, Object value) {
        return gt(getColumn(column), value);
    }

    public YxSqlPlus<T> gtEq(String column, Object value) {
        addOrAnd().append(column).append(">=?").addValue(value);
        return this;
    }

    public YxSqlPlus<T> gtEq(SFunction<T, ?> column, Object value) {
        return gtEq(getColumn(column), value);
    }

    public YxSqlPlus<T> lt(String column, Object value) {
        addOrAnd().append(column).append("<?").addValue(value);
        return this;
    }

    public YxSqlPlus<T> lt(SFunction<T, ?> column, Object value) {
        return lt(getColumn(column), value);
    }

    public YxSqlPlus<T> ltEq(String column, Object value) {
        addOrAnd().append(column).append("<=?").addValue(value);
        return this;
    }

    public YxSqlPlus<T> ltEq(SFunction<T, ?> column, Object value) {
        return ltEq(getColumn(column), value);
    }

    public YxSqlPlus<T> top(int count) {
        int select = sql.indexOf("SELECT");
        if (select < 0) {
            select = sql.indexOf("select");
        }
        sql.insert(select + 6, " top " + count + " ");
        return this;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
        reflect.setTableName(getTableName());
    }


    /**
     * @param column
     * @param date   yyyy-MM-dd
     * @return
     */
    public YxSqlPlus<T> gtDate(String column, String date) {
        addOrAnd().append(column).append(">").append("'" + date + "'");
        return this;
    }

    public YxSqlPlus<T> gtDate(SFunction<T, ?> column, String date) {
        return gtDate(getColumn(column), date);
    }

    /**
     * 大于等于  >=
     *
     * @param column
     * @param date
     * @return
     */
    public YxSqlPlus<T> gtEqDate(String column, String date) {
        addOrAnd().append(column).append(">=").append("'" + date + "'");
        return this;
    }

    public YxSqlPlus<T> gtEqDate(SFunction<T, ?> column, String date) {
        return gtEqDate(getColumn(column), date);
    }

    /**
     * 大于等于  >=
     *
     * @param column
     * @param date
     * @return
     */
    public YxSqlPlus<T> gtEqDate(String column, Date date) {
        String dateStr = MyUtils.dateToString("yyyy-MM-dd HH:mm:ss", date);
        return gtEqDate(column, dateStr);
    }

    /**
     * 大于等于  >=
     *
     * @param column
     * @param date
     * @return
     */

    public YxSqlPlus<T> gtEqDate(SFunction<T, ?> column, Date date) {
        return gtEqDate(getColumn(column), date);
    }

    /**
     * @param column
     * @param date   yyyy-MM-dd
     * @return
     */
    public YxSqlPlus<T> ltDate(String column, Date date) {
        String dateStr = MyUtils.dateToString("yyyy-MM-dd HH:mm:ss", date);
        addOrAnd().append(column).append("<").append("'" + dateStr + "'");
        return this;
    }

    public YxSqlPlus<T> ltDate(SFunction<T, ?> column, Date date) {
        return ltDate(getColumn(column), date);
    }

    /**
     * @param column
     * @param date   yyyy-MM-dd
     * @return
     */
    public YxSqlPlus<T> ltEqDate(String column, Date date) {
        String dateStr = MyUtils.dateToString("yyyy-MM-dd HH:mm:ss", date);
        addOrAnd().append(column).append("<=").append("'" + dateStr + "'");
        return this;
    }

    public YxSqlPlus<T> ltEqDate(SFunction<T, ?> column, Date date) {
        return ltEqDate(getColumn(column), date);
    }


    /**
     * 小于等于  <=
     *
     * @param column
     * @param dateStr
     * @return
     */
    public YxSqlPlus<T> ltEqDate(String column, String dateStr) {
        addOrAnd().append(column).append("<=").append("'" + dateStr + "'");
        return this;
    }

    public YxSqlPlus<T> ltEqDate(SFunction<T, ?> column, String dateStr) {
        return lt(getColumn(column), dateStr);
    }


    /**
     * @param column
     * @param date   yyyy-MM-dd
     * @return
     */
    public YxSqlPlus<T> ltDate(String column, String date) {
        addOrAnd().append(column).append("<").append("'" + date + "'");
        return this;
    }

    public YxSqlPlus<T> ltDate(SFunction<T, ?> column, String dateStr) {
        return ltDate(getColumn(column), dateStr);
    }

    private String getColumn(SFunction<T, ?> column) {
        return LambdaUtils.getColumn(column);
    }

    private String[] getColumn(SFunction<T, ?>... column) {
        String[] columns = new String[column.length];
        for (int i = 0; i < columns.length; i++) {
            columns[i] = LambdaUtils.getColumn(column[i]);
        }
        return columns;
    }


    public String getCountSql() {
        if (sql.length() == 0) count();
        String sqls = sql.toString().toLowerCase();
        if (!sqls.trim().startsWith("select count")) {
            String form = sqls.substring(sqls.indexOf("from"));
            sqls = "select count(*) as total " + form;
        }
        return sqls;
    }

    public YxSqlPlus<T> update(SFunction<T, ?>... byKeys) {
        return update(getColumn(byKeys));
    }

    public YxSqlPlus<T> update(String... byKey) {
        append("update").append(getTableName()).append("set");
        for (int i = 0; i < byKey.length; i++) {
            append(byKey[i]).append("=?,");
        }
        delLast(",");
        append("where");
        return this;
    }

    public YxSqlPlus<T> eqId(String id) {
        addOrAnd();
        try {
            Map<String, Field> ids = reflect.getTableIds();
            if (ids.size() > 1) throw new YXException("多个主键请选择 对象方式删除");
            for (Map.Entry<String, Field> entry : ids.entrySet()) {
                entry.getValue().setAccessible(true);
                append(entry.getKey()).append("=? and").addValue(id);
            }
            delLast("and");
        } catch (Exception e) {
            throw new YXException(e);
        }
        return this;
    }

    public Map<String, Field> getTableIds() {
        return reflect.getTableIds();
    }

    protected Map<String, Field> getFieldAll() {
        return reflect.getTableIds();
    }

}
